import mysql.connector

# 远程数据库连接配置
remote_conn = mysql.connector.connect(
    host="43.156.247.50",  # 替换为实际的主机名
    user="root",  # 替换为实际的用户名
    password="520777",  # 替换为实际的密码
    database="house"  # 替换为实际的数据库名
)
remote_cursor = remote_conn.cursor()

# 本地数据库连接配置
local_conn = mysql.connector.connect(
    host="127.0.0.1",  # 替换为实际的主机名
    user="root",  # 替换为实际的用户名
    password="520777",  # 替换为实际的密码
    database="house"  # 替换为实际的数据库名
)
local_cursor = local_conn.cursor()

# 每次查询的行数
batch_size = 100

def query_and_insert():
    offset = 0
    while True:
        # 从远程数据库查询数据
        remote_cursor.execute(f"SELECT * FROM house.house_follow LIMIT {batch_size} OFFSET {offset}")
        rows = remote_cursor.fetchall()

        if not rows:
            break

        # 将数据插入本地数据库
        for row in rows:
            try:
                # 构建插入语句，根据实际表结构和字段进行修改
    #             insert_query = f"INSERT INTO fang.house 
    # (house_id, `section`, block_no, unit_no, cell, price, unit_price, area, room, hall, toilet, floor, total_floor, decoration, follows_str, source, have_logs, is_del, create_time)
    # VALUES({row[0]}, {row[1]}, {row[2]}, {row[3]}, {row[4]}, {row[5]}, {row[6]}, {row[7]}, {row[8]}, {row[9]}, {row[10]}, {row[11]}, {row[12]}, {row[13]}, {row[14]}, {row[15]}, {row[16]}, {row[17]}, {row[18]}); "
                insert_query = f"INSERT INTO house.house_follow (follow_id, house_id, log_type, stars, user_name, content, create_time, create_user_id) VALUES('{row[0]}', '{row[1]}', '{row[2]}', '{row[3]}', '{row[4]}', '{row[5]}', '{row[6]}', '{row[7]}'); "
                print(insert_query)
                local_cursor.execute(insert_query)
            except Exception as e:
                if e.errno != 1062:
                    print(f"执行失败{e.message}")

        local_conn.commit()  # 提交本地数据库的更改
        offset += batch_size

    # 关闭游标和连接
    remote_cursor.close()
    remote_conn.close()
    local_cursor.close()
    local_conn.close()


if __name__ == "__main__":
    query_and_insert()
